The Fetch First N Rows Only clause in DB2™ enables optimized processing, especially for queries with potentially large result sets when only a limited number of resulting rows is requested. For example, in Fetch First 10 Rows with Order By, it would generally be more efficient to use a non-matching non-clustering index rather than a tablespace scan followed by a sort, especially for a large table with many qualifying rows. However, there can be a severe performance problem if there is no index available on an order by key. In this case, for 100 million row table, up to 100 million rows must be read and sorted, which requires many work file read and write I/O's for multiple sort/merge passes. Then only 10 rows would be read from the sorted result work file. This can consume an enormous amount of resources from both the Input/Output device and processor, which not only delays the particular query, but also potentially many other applications which may share the work file data sets.
Accordingly, there exists a need for a method for a fast fetching of ordered first N rows of a table. The method should fetch the ordered first N rows of a table without requiring work file read and write I/O's, thus providing significantly higher efficiency. The present invention addresses such a problem.